package ch.test.mysql.conn;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class MySQLConnection {
	private static Connection conn = null;
	private static String dbHost = "localhost";
	private static String dbPort = "3306";
	private static String database = "beispieldb";
	private static String dbUser = "root";
	private static String dbPassword = "";
	
	private MySQLConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + this.dbHost + ":" + this.dbPort + "/" + this.database + "?user=" + this.dbUser + "&" + this.dbPassword);
		} catch(ClassNotFoundException e) {
			System.out.println("Treiber nicht gefunden: " + e);
		} catch(SQLException e) {
			System.out.println("Connect nicht möglich: " + e);
		}
	}
	
	private static Connection getInstance() {
		if(conn == null)
			new MySQLConnection();
		return conn;
	}
	
	public static void printArtikelList() {
		conn = getInstance();
		
		if(conn != null) {
			Statement query;
			try {
				query = conn.createStatement();
				
				String sql = "SELECT bezeichnung, nettopreis AS preis FROM artikel";
				ResultSet result = query.executeQuery(sql);
				
				while(result.next()) {
					String bezeichnung = result.getString("bezeichnung");
					String preis = result.getString("preis");
					String artikel = bezeichnung + ": " + preis + " CHF";
					System.out.println(artikel);
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
